{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import pymysql\n",
    "import matplotlib.pyplot as plt\n",
    "from sqlalchemy import create_engine\n",
    "import datetime as dt\n",
    "from mplfinance.original_flavor import candlestick2_ochl\n",
    "from matplotlib.ticker import MultipleLocator \n",
    "import os\n",
    "import psycopg2 as pg \n",
    "import matplotlib.gridspec as gridspec#分割子图 \n",
    "import talib \n",
    "np.seterr(divide='ignore',invalid='ignore') # 忽略warning\n",
    "plt.rcParams['font.sans-serif']=['SimHei'] #用来正常显示中文标签 \n",
    "plt.rcParams['axes.unicode_minus']=False #用来正常显示负号 "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "2022-04-01 2020-01-25 2022-04-01 2022-04-02 2021-10-06\n"
     ]
    }
   ],
   "source": [
    "daysBefore1=-3\n",
    "daysBefore2=-2\n",
    "todayDate=(dt.datetime.now()+dt.timedelta(days=daysBefore1)).strftime('%Y-%m-%d')\n",
    "startDate=(dt.datetime.now()+dt.timedelta(days=-800)).strftime('%Y-%m-%d')\n",
    "endDate=(dt.datetime.now()+dt.timedelta(days=daysBefore1)).strftime('%Y-%m-%d')\n",
    "endDate2=(dt.datetime.now()+dt.timedelta(days=daysBefore2)).strftime('%Y-%m-%d')\n",
    "startTradeDate=(dt.datetime.now()+dt.timedelta(days=-180)).strftime('%Y-%m-%d')\n",
    "print(todayDate,startDate,endDate,endDate2,startTradeDate)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "etfdaily_features_2022_04_01_v4\n"
     ]
    }
   ],
   "source": [
    "table='etfdaily_features_%s_v4'%(todayDate.replace('-','_'))\n",
    "print(table)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "mysql+pymysql://chandliu:chandliu275288@119.45.208.182:3306/stocks?charset=utf8\n"
     ]
    }
   ],
   "source": [
    "url='mysql+pymysql://%s:%s@%s:%d/%s?charset=utf8'%('chandliu','chandliu275288','119.45.208.182',3306,'stocks')\n",
    "print(url)\n",
    "engine = create_engine(url, echo=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "select * from etfdaily_features_2022_04_01_v4 where close<10.0\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "(668, 373)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sql=\"select * from %s where close<10.0\"%table\n",
    "print(sql)\n",
    "data=pd.read_sql(sql,con=engine)\n",
    "data.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['close', 'larger_ma5', 'larger_ma10', 'larger_ma20', 'larger_ma30',\n",
       "       'larger_ma40', 'larger_ma50', 'larger_ma60', 'larger_ma70',\n",
       "       'larger_ma80',\n",
       "       ...\n",
       "       'sundays_ratio_366', 'recent_sundays', 'recent_sundays_increase',\n",
       "       'recent_neg_days', 'recent_neg_days_decrease',\n",
       "       'uppenetrate_250_increase', 'ha_sundays', 'ha_negdays',\n",
       "       'ha_invert_days', 'close_invert'],\n",
       "      dtype='object', length=371)"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "columns=data.columns[2:]\n",
    "columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "res=[]\n",
    "for col in columns:\n",
    "    tmpdict={}\n",
    "    tmpdict['max']=data[col].max()\n",
    "    tmpdict['min']=data[col].min()\n",
    "    tmpdict['mean']=data[col].mean()\n",
    "    tmpdict['median']=data[col].median()\n",
    "    tmpdict['quant_min_005']=data[col].quantile(0.05)\n",
    "    tmpdict['quant_min_01']=data[col].quantile(0.1)\n",
    "    tmpdict['quant_max_09']=data[col].quantile(0.9)\n",
    "    tmpdict['quant_max_095']=data[col].quantile(0.9)\n",
    "    tmpdf=pd.DataFrame(tmpdict, index=[col])\n",
    "#     print(tmpdf)\n",
    "    res.append(tmpdf)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                              max    min      mean  median  quant_min_005  \\\n",
      "close                       7.199  0.349  1.301278  0.9765          0.639   \n",
      "larger_ma5                224.000  0.000  4.064371  3.0000          0.000   \n",
      "larger_ma10               219.000  0.000  3.402695  1.0000          0.000   \n",
      "larger_ma20               209.000  0.000  3.300898  1.0000          0.000   \n",
      "larger_ma30               199.000  0.000  2.101796  0.0000          0.000   \n",
      "...                           ...    ...       ...     ...            ...   \n",
      "uppenetrate_250_increase   74.000  0.000  1.236527  0.0000          0.000   \n",
      "ha_sundays                228.000  0.000  3.482036  1.0000          0.000   \n",
      "ha_negdays                 79.000  0.000  1.859281  0.0000          0.000   \n",
      "ha_invert_days              8.000  0.000  0.107784  0.0000          0.000   \n",
      "close_invert                7.000  0.000  0.718563  1.0000          0.000   \n",
      "\n",
      "                          quant_min_01  quant_max_09  quant_max_095  \n",
      "close                           0.7085        2.0663         2.0663  \n",
      "larger_ma5                      0.0000        4.0000         4.0000  \n",
      "larger_ma10                     0.0000       11.0000        11.0000  \n",
      "larger_ma20                     0.0000        9.0000         9.0000  \n",
      "larger_ma30                     0.0000        4.0000         4.0000  \n",
      "...                                ...           ...            ...  \n",
      "uppenetrate_250_increase        0.0000        0.0000         0.0000  \n",
      "ha_sundays                      0.0000        4.0000         4.0000  \n",
      "ha_negdays                      0.0000        7.0000         7.0000  \n",
      "ha_invert_days                  0.0000        0.0000         0.0000  \n",
      "close_invert                    0.0000        1.0000         1.0000  \n",
      "\n",
      "[371 rows x 8 columns]\n"
     ]
    }
   ],
   "source": [
    "resdf=pd.concat(res,axis=0)\n",
    "print(resdf)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "resdf.to_excel('etf'+todayDate+'.xlsx')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
